﻿using HtmlAgilityPack;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;
using System.IO;
using System.Collections;

namespace ChineseRegionSpider
{
    class Program
    {
        static void Main(string[] args)
        {

            Console.WriteLine("开始读取新的地址数据...");
            List<Region> newRegion =
                GetNewRegionFromWeb(@"http://www.mca.gov.cn/article/sj/xzqh/2019/2019/201911250933.html");
            //List<Region> newRegion = GetJsonData();
            Console.WriteLine($"读取完毕，共 {newRegion.Count} 条");
            #region WriteJson
            var treeRegion = newRegion.Where(x => x.Code.Substring(2) == "0000").Select(x => new TreeRegion(x.Code, x.Name)).ToList();
            foreach (var p in treeRegion)
            {
                p.Children = newRegion
                    .Where(x => x.Code != p.Id && x.Code.Substring(4, 2) == "00" && x.Code.StartsWith(p.Id.Substring(0, 2)))
                    .Select(x => new TreeRegion(x.Code, x.Name)).ToList();
                var countrys = newRegion
                    .Where(x => x.Code != p.Id && x.Code.Substring(4, 2) != "00" && x.Code.StartsWith(p.Id.Substring(0, 2)))
                    .Select(x => new TreeRegion(x.Code, x.Name)).ToList();
                foreach (var city in p.Children)
                {
                    city.Children = countrys.Where(x => x.Id != city.Id && x.Id.StartsWith(city.Id.Substring(0, 4))).Select(x => new TreeRegion(x.Id, x.Name)).ToList();
                }
                foreach (var country in countrys)
                {
                    if (!p.Children.Any(x => x.Id.Substring(0, 4) == country.Id.Substring(0, 4) && x.Id.Substring(4, 2) == "00"))
                    {
                        p.Children.Add(country);
                    }
                }
            }

            File.WriteAllText(@"E:\region.json", JsonConvert.SerializeObject(treeRegion, new JsonSerializerSettings()
            {
                NullValueHandling = NullValueHandling.Ignore,
                ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
            }));

            //// serialize JSON directly to a file
            //using (StreamWriter file = File.CreateText(@"E:\region.json"))
            //{
            //    JsonSerializer serializer = new JsonSerializer();
            //    serializer.Serialize(file, newRegion);
            //}
            #endregion


            //Console.WriteLine("开始读取历史地址数据...");
            //List<Region> oldRegion = new List<Region>();
            //SqlConnection connection = new SqlConnection("Data Source=47.93.253.134,4236;Initial Catalog=ZxOld;User ID=sa;Password=hbzx@112233");
            //try
            //{
            //    connection.Open();
            //    SqlCommand command = new SqlCommand("select id,code,name from district where isactive = 1 ", connection);
            //    SqlDataAdapter adapter = new SqlDataAdapter(command);
            //    DataTable dt = new DataTable();
            //    adapter.Fill(dt);
            //    foreach (DataRow row in dt.Rows)
            //    {
            //        oldRegion.Add(new Region((int)row["id"], row["code"].ToString(), row["name"].ToString()));
            //    }
            //}
            //catch (Exception ex)
            //{
            //    Console.WriteLine($"发生错误：{ex.Message}.");
            //    Console.WriteLine($"按任意键结束....");
            //    Console.ReadKey();
            //    return;
            //}
            //finally
            //{
            //    if (connection.State != ConnectionState.Closed)
            //    {
            //        connection.Close();
            //    }
            //}
            //Console.WriteLine($"读取完毕，共 {newRegion.Count} 条");
            //Console.WriteLine("开始比对数据...");
            //var addRegions = newRegion.Except(oldRegion, new RegionComparer());
            //var deleteRegions = oldRegion.Except(newRegion, new RegionComparer());

            //Console.WriteLine($"需要插入数据{addRegions.Count()} 条，需要作废数据{deleteRegions.Count()}条。");///*,需要更新数据{updateRegions.Count}条*/

            //Console.WriteLine("按Enter继续执行，按Esc结束...");
            //while (true)
            //{
            //    var key = Console.ReadKey().Key;
            //    if (key != ConsoleKey.Enter && key != ConsoleKey.Escape)
            //    {
            //        Console.WriteLine("按Enter继续执行，按Esc结束...");
            //        continue;
            //    }
            //    else if (key == ConsoleKey.Escape)
            //    {
            //        break;
            //    }
            //    Console.WriteLine("正在执行...");
            //    try
            //    {
            //        connection.Open();
            //        foreach (var addRegion in addRegions)
            //        {
            //            string sql = $"insert into district(code,name,isactive) values('{addRegion.Code}','{addRegion.Name}',1)";
            //            SqlCommand command = new SqlCommand(sql, connection);
            //            command.ExecuteNonQuery();
            //        }
            //        foreach (var deleteRegion in deleteRegions)
            //        {
            //            string sql = $"update district set isactive = 0 where id ='{deleteRegion.Id}'";
            //            SqlCommand command = new SqlCommand(sql, connection);
            //            command.ExecuteNonQuery();
            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        Console.WriteLine($"发生错误：{ex.Message}.");
            //        Console.WriteLine($"按任意键结束....");
            //        Console.ReadKey();
            //        return;
            //    }
            //    finally
            //    {
            //        if (connection.State != ConnectionState.Closed)
            //        {
            //            connection.Close();
            //        }
            //    }

            //    break;
            //}

            //Console.WriteLine("执行完毕,按任意键结束...");
            //Console.ReadKey();
        }

        private static List<Region> GetNewRegionFromWeb(string url)
        {
            HtmlWeb web = new HtmlWeb();
            var doc = web.Load(url);
            var trs = doc.DocumentNode.SelectNodes("//html/body/div/table/tr");
            List<Region> newRegion = new List<Region>();
            foreach (var tr in trs)
            {
                if (tr.Attributes["height"].Value == "19")
                {
                    var tds = tr.ChildNodes.Where(x => x.Name == "td").ToList();
                    newRegion.Add(new Region(tds[1].InnerText.Trim(), tds[2].InnerText.Trim()));
                }
            }

            return newRegion;
        }


        private static List<Region> GetJsonData()
        {
            var data = JsonConvert.DeserializeObject<List<JsonRegion>>(File.ReadAllText(@"sql_areas.json"));
            var regions = data.Where(x => x.id != "1").Select(x => new Region(x.id, x.cname)).ToList();
            return regions;
        }


    }

    class TreeRegion
    {
        public TreeRegion(string id, string name)
        {
            this.Id = id;
            this.Name = name;
        }
        public string Id { get; set; }
        public string Name { get; set; }
        public List<TreeRegion> Children { get; set; } = null;
    }
    class JsonRegion
    {
        public string id { get; set; }
        public string cname { get; set; }
    }

    class Region
    {
        public Region(string code, string name)
        {
            this.Code = code;
            this.Name = name;
        }
        public Region(int id, string code, string name)
        {
            this.Id = id;
            this.Code = code;
            this.Name = name;
        }
        public int Id { get; set; }
        public string Code { get; set; }

        public string Name { get; set; }
    }

    // Custom comparer for the Product class
    class RegionComparer : IEqualityComparer<Region>
    {
        // Products are equal if their names and product numbers are equal.
        public bool Equals(Region x, Region y)
        {

            //Check whether the compared objects reference the same data.
            if (Object.ReferenceEquals(x, y)) return true;

            //Check whether any of the compared objects is null.
            if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
                return false;

            //Check whether the products' properties are equal.
            return x.Code == y.Code && x.Name == y.Name;
        }

        // If Equals() returns true for a pair of objects
        // then GetHashCode() must return the same value for these objects.

        public int GetHashCode(Region product)
        {
            //Check whether the object is null
            if (Object.ReferenceEquals(product, null)) return 0;

            //Get hash code for the Name field if it is not null.
            int hashProductName = product.Name == null ? 0 : product.Name.GetHashCode();

            //Get hash code for the Code field.
            int hashProductCode = product.Code.GetHashCode();

            //Calculate the hash code for the product.
            return hashProductCode ^ hashProductName;
        }

    }
}
